from model.db import reservoir_sensitivity, connect
from model.db.experiment_model import TbExperimentWater, TbExperimentWaterData, TbExperimentWaterBlocking, \
    TbExperimentSalt, TbExperimentAcid, TbExperimentAlkaline, TbExperimentSpeed, TbExperimentPressure, \
    TbExperimentWaterBlockingData, TbExperimentSaltData, TbExperimentAcidData, TbExperimentAlkalineData, \
    TbExperimentSpeedData, TbExperimentPressureData
from model.db.well_model import TbRockCore, TbCoreParam


def get_water_experiment_list(well, date, experiment_type='water'):
    """
    获取实验列表
    """
    try:
        if experiment_type == 'water':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentWater.experiment_time,
                                                TbExperimentWater.index, TbExperimentWater.id, TbExperimentWater.remark) \
                    .join(TbExperimentWater, TbRockCore.Core_ID == TbExperimentWater.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentWater.experiment_time,
                                                TbExperimentWater.index, TbExperimentWater.id, TbExperimentWater.remark) \
                    .join(TbExperimentWater, TbRockCore.Core_ID == TbExperimentWater.Core_ID)
            if date:
                exp = exp.filter(TbExperimentWater.experiment_time == date)
            return exp.all()
        if experiment_type == 'block':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentWaterBlocking.experiment_time,
                                                TbExperimentWaterBlocking.index, TbExperimentWaterBlocking.id,
                                                TbExperimentWaterBlocking.remark) \
                    .join(TbExperimentWaterBlocking, TbRockCore.Core_ID == TbExperimentWaterBlocking.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentWaterBlocking.experiment_time,
                                                TbExperimentWaterBlocking.index, TbExperimentWaterBlocking.id,
                                                TbExperimentWaterBlocking.remark) \
                    .join(TbExperimentWaterBlocking, TbRockCore.Core_ID == TbExperimentWaterBlocking.Core_ID)
            if date:
                exp = exp.filter(TbExperimentWaterBlocking.experiment_time == date)
            return exp.all()
        if experiment_type == 'salt':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentSalt.experiment_time,
                                                TbExperimentSalt.index, TbExperimentSalt.id, TbExperimentSalt.remark) \
                    .join(TbExperimentSalt, TbRockCore.Core_ID == TbExperimentSalt.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentSalt.experiment_time,
                                                TbExperimentSalt.index, TbExperimentSalt.id, TbExperimentSalt.remark) \
                    .join(TbExperimentSalt, TbRockCore.Core_ID == TbExperimentSalt.Core_ID)
            if date:
                exp = exp.filter(TbExperimentSalt.experiment_time == date)
            return exp.all()
        if experiment_type == 'acid':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentAcid.experiment_time,
                                                TbExperimentAcid.index, TbExperimentAcid.id, TbExperimentAcid.remark) \
                    .join(TbExperimentAcid, TbRockCore.Core_ID == TbExperimentAcid.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentAcid.experiment_time,
                                                TbExperimentAcid.index, TbExperimentAcid.id, TbExperimentAcid.remark) \
                    .join(TbExperimentAcid, TbRockCore.Core_ID == TbExperimentAcid.Core_ID)
            if date:
                exp = exp.filter(TbExperimentAcid.experiment_time == date)
            return exp.all()
        if experiment_type == 'alkaline':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentAlkaline.experiment_time,
                                                TbExperimentAlkaline.index, TbExperimentAlkaline.id,
                                                TbExperimentAlkaline.remark) \
                    .join(TbExperimentAlkaline, TbRockCore.Core_ID == TbExperimentAlkaline.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentAlkaline.experiment_time,
                                                TbExperimentAlkaline.index, TbExperimentAlkaline.id,
                                                TbExperimentAlkaline.remark) \
                    .join(TbExperimentAlkaline, TbRockCore.Core_ID == TbExperimentAlkaline.Core_ID)
            if date:
                exp = exp.filter(TbExperimentAlkaline.experiment_time == date)
            return exp.all()
        if experiment_type == 'speed':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentSpeed.experiment_time,
                                                TbExperimentSpeed.index, TbExperimentSpeed.id, TbExperimentSpeed.remark) \
                    .join(TbExperimentSpeed, TbRockCore.Core_ID == TbExperimentSpeed.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentSpeed.experiment_time,
                                                TbExperimentSpeed.index, TbExperimentSpeed.id, TbExperimentSpeed.remark) \
                    .join(TbExperimentSpeed, TbRockCore.Core_ID == TbExperimentSpeed.Core_ID)
            if date:
                exp = exp.filter(TbExperimentSpeed.experiment_time == date)
            return exp.all()
        if experiment_type == 'pressure':
            if len(well) != 0:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentPressure.experiment_time,
                                                TbExperimentPressure.index, TbExperimentPressure.id,
                                                TbExperimentPressure.remark) \
                    .join(TbExperimentPressure, TbRockCore.Core_ID == TbExperimentPressure.Core_ID) \
                    .filter(TbRockCore.Belong_Well_Name.in_(well))
            else:
                exp = connect.projSession.query(TbRockCore.Core_ID, TbRockCore.Core_Name,
                                                TbExperimentPressure.experiment_time,
                                                TbExperimentPressure.index, TbExperimentPressure.id,
                                                TbExperimentPressure.remark) \
                    .join(TbExperimentPressure, TbRockCore.Core_ID == TbExperimentPressure.Core_ID)
            if date:
                exp = exp.filter(TbExperimentPressure.experiment_time == date)
            return exp.all()
    except:
        connect.projSession.rollback()


def add_water_experiment(core_id, date, experiment_type='water'):
    """
    add水敏实验
    """
    if experiment_type == 'water':
        new_core = TbExperimentWater(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'block':
        new_core = TbExperimentWaterBlocking(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'salt':
        new_core = TbExperimentSalt(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'acid':
        new_core = TbExperimentAcid(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'alkaline':
        new_core = TbExperimentAlkaline(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'speed':
        new_core = TbExperimentSpeed(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core
    if experiment_type == 'pressure':
        new_core = TbExperimentPressure(
            Core_ID=core_id,
            experiment_time=date
        )
        connect.projSession.add(new_core)
        connect.projSession.commit()
        connect.projSession.flush(new_core)
        return new_core


def updateWaterExperimentBasic(delete, update, experiment_type='water'):
    """
     更新水敏实验
     """
    try:
        if experiment_type == 'water':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentWater).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentWater).filter(TbExperimentWater.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()
        if experiment_type == 'block':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentWaterBlocking).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentWaterBlocking).filter(
                        TbExperimentWaterBlocking.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()

        if experiment_type == 'salt':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentSalt).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentSalt).filter(TbExperimentSalt.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()
        if experiment_type == 'acid':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentAcid).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentAcid).filter(TbExperimentAcid.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()
        if experiment_type == 'alkaline':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentAlkaline).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentAlkaline).filter(TbExperimentAlkaline.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()
        if experiment_type == 'speed':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentSpeed).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentSpeed).filter(TbExperimentSpeed.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()
        if experiment_type == 'pressure':
            if len(delete) != 0:
                for item in delete:
                    deleteItem = connect.projSession.query(TbExperimentPressure).filter_by(id=item['id']).first()
                    connect.projSession.delete(deleteItem)
                    connect.projSession.commit()
            if len(update) != 0:
                for item in update:
                    connect.projSession.query(TbExperimentPressure).filter(TbExperimentPressure.id == item['id']) \
                        .update({
                        'experiment_time': item['experiment_time'],
                        'index': item['index'],
                        'remark': item['remark']
                    })
                    connect.projSession.commit()
                    connect.projSession.flush()

    except Exception as e:
        connect.projSession.rollback()
        raise e


def get_water_experiment(id, experiment_type='water'):
    """
    获取水敏实验数据
    """
    if experiment_type == 'water':
        result = connect.projSession.query(TbExperimentWater.id, TbRockCore.Core_Name,
                                           TbExperimentWater.experiment_time,
                                           TbExperimentWater.index, TbExperimentWater.remark).join(TbExperimentWater,
                                                                                                   TbExperimentWater.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentWater.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentWaterData.inject_fluid,
                                                    TbExperimentWaterData.Inject_volume,
                                                    TbExperimentWaterData.permeability,
                                                    TbExperimentWaterData.damage_ratio).filter(
            TbExperimentWaterData.experiment_ID == id).order_by(TbExperimentWaterData.Inject_volume).all()
        return result, experiment_data
    if experiment_type == 'block':
        result = connect.projSession.query(TbExperimentWaterBlocking.id, TbRockCore.Core_Name,
                                           TbExperimentWaterBlocking.experiment_time,
                                           TbExperimentWaterBlocking.index, TbExperimentWaterBlocking.remark).join(
            TbExperimentWaterBlocking,
            TbExperimentWaterBlocking.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentWaterBlocking.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentWaterBlockingData).filter(
            TbExperimentWaterBlockingData.experiment_ID == id).all()
        return result, experiment_data
    if experiment_type == 'salt':
        result = connect.projSession.query(TbExperimentSalt.id, TbRockCore.Core_Name,
                                           TbExperimentSalt.experiment_time,
                                           TbExperimentSalt.index, TbExperimentSalt.remark).join(TbExperimentSalt,
                                                                                                 TbExperimentSalt.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentSalt.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentSaltData.remark,
                                                    TbExperimentSaltData.critical_mineral_concentration,
                                                    TbExperimentSaltData.permeability,
                                                    TbExperimentSaltData.damage_ratio).filter(
            TbExperimentSaltData.experiment_ID == id).all()
        return result, experiment_data
    if experiment_type == 'acid':
        result = connect.projSession.query(TbExperimentAcid.id, TbRockCore.Core_Name,
                                           TbExperimentAcid.experiment_time,
                                           TbExperimentAcid.index, TbExperimentAcid.remark).join(TbExperimentAcid,
                                                                                                 TbExperimentAcid.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentAcid.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentAcidData).filter(
            TbExperimentAcidData.experiment_ID == id).all()
        return result, experiment_data
    if experiment_type == 'alkaline':
        result = connect.projSession.query(TbExperimentAlkaline.id, TbRockCore.Core_Name,
                                           TbExperimentAlkaline.experiment_time,
                                           TbExperimentAlkaline.index, TbExperimentAlkaline.remark).join(
            TbExperimentAlkaline,
            TbExperimentAlkaline.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentAlkaline.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentAlkalineData).filter(
            TbExperimentAlkalineData.experiment_ID == id).all()
        return result, experiment_data
    if experiment_type == 'speed':
        result = connect.projSession.query(TbExperimentSpeed.id, TbRockCore.Core_Name,
                                           TbExperimentSpeed.experiment_time,
                                           TbExperimentSpeed.index, TbExperimentSpeed.remark).join(TbExperimentSpeed,
                                                                                                   TbExperimentSpeed.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentSpeed.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentSpeedData).filter(
            TbExperimentSpeedData.experiment_ID == id).all()
        return result, experiment_data
    if experiment_type == 'pressure':
        result = connect.projSession.query(TbExperimentPressure.id, TbRockCore.Core_Name,
                                           TbExperimentPressure.experiment_time,
                                           TbExperimentPressure.index, TbExperimentPressure.remark).join(
            TbExperimentPressure,
            TbExperimentPressure.Core_ID == TbRockCore.Core_ID).filter(
            TbExperimentPressure.Core_ID == id).first()
        experiment_data = connect.projSession.query(TbExperimentPressureData).filter(
            TbExperimentPressureData.experiment_ID == id).all()
        return result, experiment_data


def get_water_experimentbycore(id, experiment_type='water'):
    """
    通过岩芯获取水敏实验
    """
    if experiment_type == 'water':
        result = connect.projSession.query(TbExperimentWater).filter(TbExperimentWater.Core_ID == id).first()
    if experiment_type == 'block':
        result = connect.projSession.query(TbExperimentWaterBlocking).filter(
            TbExperimentWaterBlocking.Core_ID == id).first()
    if experiment_type == 'salt':
        result = connect.projSession.query(TbExperimentSalt).filter(TbExperimentSalt.Core_ID == id).first()
    if experiment_type == 'acid':
        result = connect.projSession.query(TbExperimentAcid).filter(TbExperimentAcid.Core_ID == id).first()
    if experiment_type == 'alkaline':
        result = connect.projSession.query(TbExperimentAlkaline).filter(TbExperimentAlkaline.Core_ID == id).first()
    if experiment_type == 'speed':
        result = connect.projSession.query(TbExperimentSpeed).filter(TbExperimentSpeed.Core_ID == id).first()
    if experiment_type == 'pressure':
        result = connect.projSession.query(TbExperimentPressure).filter(TbExperimentPressure.Core_ID == id).first()
    return result


def update_water_experiment(param, experiment_type='water'):
    """
    更新水敏实验
    """
    if experiment_type == 'water':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentWaterData).filter(TbExperimentWaterData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentWaterData(
                    experiment_ID=param.id,
                    inject_fluid=data['inject_fluid'],
                    Inject_volume=data['Inject_volume'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'block':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentWaterBlockingData).filter(
            TbExperimentWaterBlockingData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentWaterBlockingData(
                    experiment_ID=param.id,
                    mobile_water_saturation=data['mobile_water_saturation'],
                    mobile_water_damage=data['mobile_water_damage'],
                    immobile_water_saturation=data['immobile_water_saturation'],
                    immobile_water_damage=data['immobile_water_damage'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'salt':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentSaltData).filter(TbExperimentSaltData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentSaltData(
                    experiment_ID=param.id,
                    remark=data['remark'],
                    critical_mineral_concentration=data['critical_mineral_concentration'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'acid':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentAcidData).filter(TbExperimentAcidData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentAcidData(
                    experiment_ID=param.id,
                    acid_concentration=data['acid_concentration'],
                    remark=data['remark'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'alkaline':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentAlkalineData).filter(
            TbExperimentAlkalineData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentAlkalineData(
                    experiment_ID=param.id,
                    PH=data['PH'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'speed':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentSpeedData).filter(TbExperimentSpeedData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentSpeedData(
                    experiment_ID=param.id,
                    speed=data['speed'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()
    if experiment_type == 'pressure':
        # 删除旧的实验数据
        connect.projSession.query(TbExperimentPressureData).filter(
            TbExperimentPressureData.experiment_ID == param.id).delete(
            synchronize_session=False)
        # 新增新的实验数据
        if len(param.data) != 0:
            for data in param.data:
                newData = TbExperimentPressureData(
                    experiment_ID=param.id,
                    pressure=data['pressure'],
                    permeability=data['permeability'],
                    damage_ratio=data['damage_ratio'],
                )
                connect.projSession.add(newData)
        connect.projSession.commit()

    return 1


# 获取岩芯参数
def get_core_param(Parameter_Ecode):
    parm = connect.projSession.query(TbCoreParam) \
        .filter(TbCoreParam.Parameter_Ecode == Parameter_Ecode).first()
    return parm
